#!/usr/bin/env python
# -*- coding: utf-8 -*-
# Purpose: HydroPlatform database model
# Created: 11/18/2008
# Copyright (c) 2008,2009 by University College London
# Authors:
# Didrik Pinte <dpinte@dipole-consulting.com>
#
# This program is free software under the GPL (>=v2)
# Read the file COPYING coming with HydroPlatform for details.
"""
SQLAlchemy database description and mapping between network classes 
and Table objects
"""
import logging
import os

import sqlalchemy
from sqlalchemy import create_engine
from sqlalchemy import Table, Column, MetaData, ForeignKey, \
     UniqueConstraint, Integer, Unicode, PickleType, Text, \
     FLOAT, Boolean, Date
from sqlalchemy.orm import mapper, sessionmaker, relation, backref

from hydroplatform.model.network import Network, Node, NodeField, NodeType, \
     NodeValue, Link, Project, FieldGroup, LinkType, LinkField, LinkValue
from hydroplatform.model.units import Unit

metadata = MetaData()

project_table = Table('projects', metadata, 
        Column('id', Integer, primary_key=True),
        Column('name', Unicode(100), nullable=False),
        Column('description', Text, nullable=True),
        Column('limit_to_8', Boolean, default=False),
        Column('horizon_from', Date, nullable=True), 
        Column('horizon_to', Date, nullable=True), 
        Column('time_step', Integer, nullable=True),
        Column('projection_params', PickleType(mutable=False), nullable=True),
        Column('projection_name', Unicode(250), nullable=True),
        Column('projection_epsg', Unicode(25), nullable=True)
)

network_table = Table('networks', metadata,
        Column('id', Integer, primary_key=True),
        Column('name', Unicode(100), nullable=False),
        Column('project_id', Integer, ForeignKey('projects.id'), \
               nullable=False),
        Column('description', Text, nullable=True)
)

nodetype_table = Table('nodetypes', metadata,
        Column('id', Integer, primary_key=True),
        Column('name', Unicode(100), nullable=False),
        Column('description', Text, nullable=True),
        Column('project_id', Integer, ForeignKey('projects.id'), \
               nullable=False),
        Column('shape', Unicode(25), nullable=True),
        Column('pen', Unicode(25), nullable=True),
        Column('brush', Unicode(25), nullable=True),
        Column('size', Integer, nullable=True),
        Column('visible_on_map', Boolean, default=True),
        UniqueConstraint('name', 'project_id')
)

linktype_table = Table('linktypes', metadata,
        Column('id', Integer, primary_key=True),
        Column('name', Unicode(100), nullable=False),
        Column('description', Text, nullable=True),
        Column('pen', Unicode(25), nullable=True),
        Column('size', Integer, nullable=True),
        Column('style', Unicode(25), nullable=True),
        Column('project_id', Integer, ForeignKey('projects.id'), \
               nullable=False),
        UniqueConstraint('name', 'project_id')
)

nodetypefield_table = Table('nodetypefields', metadata,
        Column('id', Integer, primary_key=True),
        Column('node_type_id', Integer, ForeignKey('nodetypes.id'), \
               nullable=False),
        Column('attribute_type', PickleType, nullable=False),
        Column('name', Unicode(25), nullable=False),
        Column('group_id', Integer, ForeignKey('fieldgroups.id'), \
               nullable=False),
        UniqueConstraint('name', 'node_type_id')
)

linktypefield_table = Table('linktypefields', metadata,
        Column('id', Integer, primary_key=True),
        Column('link_type_id', Integer, ForeignKey('linktypes.id'), \
               nullable=False),
        Column('attribute_type', PickleType, nullable=False),
        Column('name', Unicode(25), nullable=False),
        Column('group_id', Integer, ForeignKey('fieldgroups.id'), \
               nullable=False),
        UniqueConstraint('name', 'link_type_id')
)

fieldgroup_table = Table('fieldgroups', metadata,
        Column('id', Integer, primary_key=True),
        Column('name', Unicode(20), nullable=False)
)

nodetype_fieldgroup_table = Table('ntype_groupe', metadata,
        Column('node_type_id', Integer, ForeignKey('nodetypes.id'),
               nullable=False, primary_key=True),
        Column('group_id', Integer, ForeignKey('fieldgroups.id'), 
               nullable=False, primary_key=True)
)

linktype_fieldgroup_table = Table('ltype_groupe', metadata,
        Column('link_type_id', Integer, ForeignKey('linktypes.id'),
               nullable=False, primary_key=True),
        Column('group_id', Integer, ForeignKey('fieldgroups.id'), 
               nullable=False, primary_key=True)
)

nodeattribute_table = Table('nodeattributes', metadata, 
        Column('node_id', Integer, ForeignKey('nodes.id'), primary_key=True),
        Column('attribute_id', Integer, ForeignKey('nodetypefields.id'), \
               primary_key=True),
        Column('value', PickleType)
)


linkattribute_table = Table('linkattributes', metadata, 
        Column('link_id', Integer, ForeignKey('links.id'), primary_key=True),
        Column('attribute_id', Integer, ForeignKey('linktypefields.id'), \
               primary_key=True),
        Column('value', PickleType)
)
    
node_table = Table('nodes', metadata, 
        Column('id', Integer, primary_key=True),
        Column('project_id', Integer, ForeignKey('projects.id'), \
               nullable=False),
        Column('type_id', Integer, ForeignKey('nodetypes.id'), nullable=False),
        Column('name', Unicode(100), nullable=False),
        Column('notes', Text, nullable=True, unique=False),
        Column('x', FLOAT, nullable=True),
        Column('y', FLOAT, nullable=True),
        UniqueConstraint('name', 'project_id')
) 

link_table = Table('links', metadata, 
        Column('id', Integer, primary_key=True),
        Column('project_id', Integer, ForeignKey('projects.id'), \
               nullable=False),
        Column('start_node_id', Integer, ForeignKey('nodes.id'), \
               nullable=False),
        Column('end_node_id', Integer, ForeignKey('nodes.id'), nullable=False),
        Column('type_id', Integer, ForeignKey('linktypes.id'), nullable=False),
        Column('geometry', Text, nullable=True),
        Column('name', Unicode(100), nullable=False, unique=False),
        Column('notes', Text, nullable=True, unique=False),
        UniqueConstraint('name', 'project_id')
)

network_node_table = Table('network_nodes', metadata,
        Column('network_id', ForeignKey('networks.id'), primary_key=True),
        Column('node_id', ForeignKey('nodes.id'), primary_key=True)
)

network_link_table = Table('network_links', metadata,
        Column('network_id', ForeignKey('networks.id'), primary_key=True),
        Column('link_id', ForeignKey('links.id'), primary_key=True)
)

nodetype_linktype_table = Table('nodetypes_linktypes', metadata,
        Column('nodetype_id', ForeignKey('nodetypes.id'), primary_key=True),
        Column('linktype_id', ForeignKey('linktypes.id'), primary_key=True)
)


units_table = Table('units', metadata,
        Column('id', Integer, primary_key=True),
        Column('name', Unicode(20), unique=True, nullable=False),
        Column('description', Text, nullable=True, unique=False),
        Column('category', Text, nullable=False, unique=False),
        )

project_units_table = Table('project_units', metadata,
            Column('project_id', ForeignKey('projects.id'), nullable=False, \
                   primary_key=True),
            Column('unit_id', ForeignKey('units.id'), nullable=False, \
                   primary_key=True))
    
mapper(Project, project_table,
       properties = {
           'networks': relation(Network, cascade="all,delete"),
           'nodetypes': relation(NodeType, cascade="all,delete"),
           'linktypes': relation(LinkType, cascade="all,delete"),
           'nodes' : relation(Node, cascade="all,delete"),
           'links' : relation(Link),
           'units' : relation(Unit, secondary=project_units_table)
   })        

mapper(NodeType, nodetype_table, properties ={
        'groups': relation(FieldGroup, secondary=nodetype_fieldgroup_table),
        'linktypes' : relation(LinkType, secondary=nodetype_linktype_table),
      })

mapper(NodeField, nodetypefield_table, properties={
       'type' : relation(NodeType, backref=backref('fields', \
                                                   cascade="all,delete"))
    })

mapper(FieldGroup, fieldgroup_table, properties={
       'nodefields' : relation(NodeField, backref=backref('group')),
       'linkfields' : relation(LinkField, backref=backref('group'))
    })

mapper(NodeValue, nodeattribute_table, properties = {
       'field': relation(NodeField, 
            primaryjoin = \
            nodeattribute_table.c.attribute_id== nodetypefield_table.c.id)
    })

mapper(Network, network_table, properties = {
           'project': relation(Project),
           'nodes' : relation(Node, secondary=network_node_table, \
                              backref="networks"),
           'links' : relation(Link, secondary=network_link_table, \
                              backref="networks")
   }) 

mapper(Node, node_table,
       properties = {
           'values': relation(NodeValue, backref="node"),
           'type': relation(NodeType, backref="nodes"),
   })    

mapper(Link, link_table, properties = {
       'start' : relation(Node, \
                primaryjoin=link_table.c.start_node_id==node_table.c.id),
       'end' : relation(Node, \
                primaryjoin=link_table.c.end_node_id==node_table.c.id),
       'values': relation(LinkValue),
       'type' : relation(LinkType, backref="links")
})

mapper(LinkType, linktype_table, {
        'groups': relation(FieldGroup, secondary=linktype_fieldgroup_table)
})

mapper(LinkField, linktypefield_table, properties={
       'type' : relation(LinkType, backref=backref('fields', \
                                                   cascade="all,delete"))
    })

mapper(LinkValue, linkattribute_table, properties = {
       'field': relation(LinkField, 
                primaryjoin = \
                linkattribute_table.c.attribute_id== linktypefield_table.c.id)
    })

mapper(Unit, units_table)

engine = None
session = None
def get_session(connection_string):
    """
    Returns a sqlalchemy session object based on a given engine
    
    FIXME : should not use variable but a Singleton class would work fine
    """
    engine = create_engine(connection_string)
    check_and_upgrade(connection_string)
    Session = sessionmaker(bind=engine, autoflush=True, autocommit=False)
    return Session()


def check_and_upgrade(url):
    """
    Depending on the versions add or remove columns
    """
    from migrate.versioning import api
    import sys
    if hasattr(sys, 'frozen'):
        repository = os.path.join(os.path.dirname(sys.executable), "db_repository")        
    else:
        repository = os.path.join(os.path.dirname(__file__), "db_repository")
    print repository
    try:
        version = api.db_version(url, repository)
        logging.info("Database runs version %s" % version)
    except sqlalchemy.exc.NoSuchTableError, e:
        logging.warning("Adding version control to the table")
        # means no versioning
        logging.info(api.version_control(url, repository))
        logging.info(api.db_version(url, repository))
    
    api.upgrade(url, repository)    
    
if __name__ == '__main__':
    ENGINE = create_engine('sqlite:///:memory:', echo=True)
    metadata.create_all(ENGINE)
    Session = sessionmaker(bind=engine, autoflush=True, transactional=True)
    test_session = Session()
    p1 = Project('project 1', 'this is a test project')
    test_session.add(p1)
    test_session.add(Project('project 1', 'this is a test project'))
    test_session.add(Node('dam 1'))
    test_session.add(Node('dam 2'))
    test_session.add(Node('dam 3'))
    test_session.add(Link(1, 1, 2))
    test_session.add(Link(1, 2, 3))
